package com.swn.common.util;

import java.util.List;
import java.util.Map;
/**
 * 根据传递的map动态组装单表的insert/update的sql语句
 * 
 * @author taozhaocai
 * @Date 2016-6-13  下午2:07:23
 * @Note <这里是该类的使用注意事项>

 */
public class SqlUtil {
	/**
	 * 获取具名参数方式的insert_sql
	 * 
	 * @param colCodeList:当前表包含字段的列集合
	 * @param map：传递过来的map
	 * @param tableName:操作表代码
	 * @return String
	 *
	 * @Author taozhaocai
	 * @Date 2016-6-13 下午2:02:54
	 */
	public static String getAddNamedSql(List<String> colCodeList,Map<String, Object> map,
			String tableName) {
		
		StringBuffer sql = new StringBuffer();
		StringBuffer dynamicCol = new StringBuffer();
		StringBuffer dynamicNamedCol = new StringBuffer();
		sql.append("INSERT INTO ").append(tableName).append(" (");
		for (String key : map.keySet()) {
			//排除掉多传递的map值
			if(!colCodeList.contains(key)){
				continue;
			}
			dynamicCol.append(key).append(",");
			dynamicNamedCol.append(":").append(key).append(",");

		}
		String dynamicColStr = dynamicCol.substring(0,dynamicCol.length()-1);
		String dynamicNamedColStr = dynamicNamedCol.substring(0,dynamicNamedCol.length()-1);
		sql.append(dynamicColStr).append(") VALUES (").append(dynamicNamedColStr)
		.append(")");

		return sql.toString();
	}
	
	
	
	/**
	 * 获取具名参数方式的update_sql
	 * 
	 * @param colCodeList:当前表包含字段的列集合
	 * @param map：传递过来的map
	 * @param tableName:操作表代码
	 * @param keyCol：具名参数方式的主键字符串sql
	 * @return String
	 *
	 * @Author taozhaocai
	 * @Date 2016-6-13 下午1:59:57
	 */
	public static String getUpdateNamedSql(List<String> colCodeList,Map<String, Object> map,
			String tableName,String keyCol) {
		StringBuffer sql = new StringBuffer();
		StringBuffer dynamicNamedCol = new StringBuffer();
		sql.append("UPDATE ").append(tableName).append(" SET ");
		for (Object key : map.keySet()) {
			//排除掉多传递的map值
			if(!colCodeList.contains(key)){
				continue;
			}
			dynamicNamedCol.append(key).append("=").append(":").append(key).append(",");

		}
		String dynamicNamedColStr = dynamicNamedCol.toString().substring(0,dynamicNamedCol.toString().length()-1);
		sql.append(dynamicNamedColStr).append(" WHERE ").append(keyCol);

		
		return sql.toString();
	}
	
	public static void main(String[] args) {
		String s = "abcdf,";
		System.out.println(s.substring(0,s.length()-1));
	}
}
